-- 创建任务表
DROP TABLE IF EXISTS `SYSTEM_JOB`;
CREATE TABLE `SYSTEM_JOB` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `JOB_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'JOB名称',
  `JOB_DESCRIPTION` varchar(500) DEFAULT NULL COMMENT 'JOB描述',
  `HANDLER` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务处理器类',
  `DISTRIBUTE_HANDLER` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '数据分发处理器',
  `DISTRIBUTE_LIMIT` int DEFAULT NULL COMMENT '分发数限制',
  `DISALLOW_CONCURRENT` tinyint(1) NOT NULL COMMENT '是否禁用并发执行',
  `DISABLED` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否禁用',
  `WITH_ASYNC` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否开启异步执行',
  `WITH_LOG` tinyint(1) NOT NULL COMMENT '是否记录日志',
  `CRON_EXPRESSION` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'CRON表达式',
  `ALLOW_SERVER_IPS` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '服务器白名单',
  `STATUS` tinyint DEFAULT NULL COMMENT '状态（-1暂停，0准备，1运行中）',
  `NEXT_FIRE_TIME` datetime DEFAULT NULL COMMENT '预计下一次执行时间',
  `LOCK_NUMBER` int NOT NULL DEFAULT '0' COMMENT '锁编号',
  `CREATE_USER` int NOT NULL COMMENT '创建人',
  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',
  `UPDATE_USER` int DEFAULT NULL COMMENT '更新人',
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='系统定时任务';

BEGIN;
INSERT INTO `SYSTEM_JOB` VALUES (1, '测试任务1', '测试任务', 'commonTestJob', NULL, NULL, 1, 0, 1, 1, '0/10 * * * * ?', '', 0, '2022-04-30 10:22:10', 4147, 1, '2022-02-20 20:13:20', 1, '2022-04-29 11:15:05', 0);
INSERT INTO `SYSTEM_JOB` VALUES (2, '测试任务2', '带分片的任务', 'distributableTestJob', 'distributableTestJobDistributer', 10, 1, 0, 0, 1, '0/10 * * * * ?', '', 0, '2022-04-30 10:22:10', 11157, 1, '2022-02-28 22:05:14', 1, '2022-04-29 11:24:38', 0);
COMMIT;

-- 创建任务日志表
DROP TABLE IF EXISTS `SYSTEM_JOB_LOG`;
CREATE TABLE `SYSTEM_JOB_LOG` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `JOB_ID` int NOT NULL COMMENT '定时任务ID',
  `JOB_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '任务名称',
  `JOB_DISTRIBUTE_GROUP` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分发组',
  `SNIPPET_ID` int DEFAULT NULL COMMENT '分片ID',
  `SNIPPET_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分片编码',
  `BUSINESS_TIME` datetime DEFAULT NULL COMMENT '业务时间戳',
  `RUN_TIME_START` datetime NOT NULL COMMENT '执行开始时间',
  `RUN_TIME_END` datetime DEFAULT NULL COMMENT '执行结束时间',
  `RUN_SPEND_TIME` int DEFAULT NULL COMMENT '耗时',
  `PARAMETERS` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '执行参数',
  `HANDLE_TOTAL_SIZE` int DEFAULT NULL COMMENT '处理总数',
  `HANDLE_SUCCESS_SIZE` int DEFAULT NULL COMMENT '处理成功数',
  `SERVER_IP` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '执行任务的服务器IP',
  `STATUS` tinyint NOT NULL COMMENT '状态（0执行失败，1执行成功，2跳过，-1执行中）',
  `CONTEXT` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '任务执行上下文',
  `TRIGGER_TYPE` varchar(10) NOT NULL COMMENT '触发类型，SYSTEM-系统定时触发，MANUAL-手动触发',
  `REMARK` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '备注',
  `CREATE_TIME` datetime NOT NULL COMMENT '日志创建时间',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统定时任务日志';

-- 创建任务分片表
DROP TABLE IF EXISTS `SYSTEM_JOB_SNIPPET`;
CREATE TABLE `SYSTEM_JOB_SNIPPET` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `JOB_ID` int NOT NULL COMMENT '定时任务ID',
  `JOB_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'JOB名称',
  `JOB_DISTRIBUTE_GROUP` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分发组',
  `JOB_BUSINESS_TIME` datetime NOT NULL COMMENT '任务执行业务时间',
  `HANDLER` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分片处理器类',
  `DISTRIBUTE_HANDLER` varchar(200) DEFAULT NULL COMMENT '数据分发处理器',
  `DISTRIBUTE_LIMIT` int DEFAULT NULL COMMENT '分发数限制',
  `SNIPPET_INDEX` int NOT NULL COMMENT '分片索引',
  `SNIPPET_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分片编号',
  `SNIPPET_DATA` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '分片数据',
  `SNIPPET_DATA_SIZE` int DEFAULT '0' COMMENT '分片数据数量',
  `WITH_LOG` tinyint(1) NOT NULL COMMENT '是否记录日志',
  `ALLOW_SERVER_IPS` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '服务器白名单',
  `STATUS` tinyint NOT NULL COMMENT '分片状态（0准备，1运行中，2已完成）',
  `LOCK_NUMBER` int NOT NULL DEFAULT '0' COMMENT '锁编号',
  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='定时任务片段';

-- 添加菜单
INSERT INTO `SYSTEM_MENU` VALUES (14, 9, '定时任务', '', '系统初始化创建', 'el-icon-alarm-clock', 0, 4, 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_MENU` VALUES (15, 14, '任务管理', '/system/job', '系统初始化创建', 'el-icon-alarm-clock', 0, 0, 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_MENU` VALUES (16, 14, '分片管理', '/system/snippet', '系统初始化创建', 'el-icon-timer', 0, 1, 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_MENU` VALUES (17, 14, '任务日志', '/system/job-log', '系统初始化创建', 'eva-icon-log', 0, 2, 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);

-- 为超级管理员分配菜单权限
INSERT INTO `SYSTEM_ROLE_MENU` VALUES (14, 1, 14, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_MENU` VALUES (15, 1, 15, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_MENU` VALUES (16, 1, 16, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_MENU` VALUES (17, 1, 17, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);

-- 添加权限
INSERT INTO `SYSTEM_PERMISSION` VALUES (50, 'system:job:trigger', '手动触发任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (51, 'system:job:create', '创建任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (52, 'system:job:delete', '删除任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (53, 'system:job:update', '修改任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (54, 'system:job:query', '查询任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (55, 'system:job:snippet:delete', '删除分片任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (56, 'system:job:snippet:query', '查询分片任务', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_PERMISSION` VALUES (57, 'system:job:log:query', '查询任务日志', '任务管理', '系统初始化创建', 0, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);

-- 为超级管理员分配权限
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (50, 1, 50, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (51, 1, 51, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (52, 1, 52, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (53, 1, 53, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (54, 1, 54, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (55, 1, 55, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (56, 1, 56, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);
INSERT INTO `SYSTEM_ROLE_PERMISSION` VALUES (57, 1, 57, 1, CURRENT_TIMESTAMP, NULL, NULL, 0);